﻿using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Windows.Forms;

namespace DXApplicationTangche.Utils
{
    public class ExcelUtil
    {
        /// <summary>
        /// 取excel数据
        /// </summary>
        /// <returns></returns>
        public static DataSet getExcelDatas(String FileName) {
            //读取excel数据
            using (var stream = File.Open(FileName, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    // 1. Use the reader methods
                    do
                    {
                        while (reader.Read())
                        {
                            // reader.GetDouble(0);
                        }
                    } while (reader.NextResult());

                    // 2. Use the AsDataSet extension method
                    return reader.AsDataSet();
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static ExcelAndPath getExcelDatasAndPath()
        {
            //设置弹框属性，限制文件后缀名，是否多选
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = MIMEConst.EXCEL_MIME;
            openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            openFile.Multiselect = false;
            //读取excel数据
            if (openFile.ShowDialog() == DialogResult.Cancel) return null;
            using (var stream = File.Open(openFile.FileName, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    // 1. Use the reader methods
                    do
                    {
                        while (reader.Read())
                        {
                            // reader.GetDouble(0);
                        }
                    } while (reader.NextResult());

                    // 2. Use the AsDataSet extension method
                    return new ExcelAndPath(reader.AsDataSet(), System.IO.Path.GetDirectoryName(openFile.FileName), System.IO.Path.GetFileName(openFile.FileName));

                }
            }
        }
        /// <summary>
        /// 取Excel数据和路径
        /// </summary>
        /// <returns></returns>

        public static ExcelAndPath getExcelDataAndPath(String FileName)
        {
            using (var stream = File.Open(FileName, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    // 1. Use the reader methods
                    do
                    {
                        while (reader.Read())
                        {
                            // reader.GetDouble(0);
                        }
                    } while (reader.NextResult());

                    // 2. Use the AsDataSet extension method
                    return new ExcelAndPath(reader.AsDataSet(), System.IO.Path.GetDirectoryName(FileName), System.IO.Path.GetFileName(FileName));

                }
            }
        }
        /// <summary>
        /// 导数据到Excel模板
        /// </summary>
        /// <param name="tab">要输出内容的Table</param>
        /// <param name="fileName">生成后的路径带文件名</param>
        /// <param name="filePath">模板路径带文件名</param>
        /// <param name="lineNumber">表头占了几行</param>
        /// <param name="tableItem">Excel的第几个工作薄</param>
        /// <param name="columns">要遍历的列名</param>
        public static void ToExcelTemplate(DataTable tab, string fileName, string filePath, int lineNumber, int tableItem, List<String> columns)
        {
            //需要添加 Microsoft.Office.Interop.Excel引用 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

            if (app == null)
            {
                HttpContext.Current.Response.Write("服务器上缺少Excel组件，需要安装Office软件。");
                return;
            }
            app.Visible = false;
            app.UserControl = true;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
            Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(System.AppDomain.CurrentDomain.BaseDirectory + filePath); //加载模板
            Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
            Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(tableItem); //第几个工作薄。

            if (worksheet == null)
                return;  //工作薄中没有工作表.
            //1、获取数据。
            if (tab.Rows.Count < 1)
                return; //没有数据，不需要导出。
            //表头
            worksheet.Cells[1, 1] = "导出时间：" + DateTime.Now.ToString();
            //2、写入数据，Excel索引从1开始。
            for (int i = 0; i <= tab.Rows.Count - 1; i++)
            {
                //循环每一列
                for (int j = 0; j < columns.Count; j++)
                {
                    worksheet.Cells[lineNumber + i, j + 1] = tab.Rows[i][columns[j]].ToString();
                }
            }

            //调整Excel的样式。
            //rg.Borders.LineStyle = 1; //单元格加边框。
            worksheet.Columns.AutoFit(); //自动调整列宽。

            //3、保存生成的Excel文件。
            //Missing 在System.Reflection命名空间下。
            string savaPath = fileName;
            //如果已经有了就删除原来的
            if (File.Exists(Path.GetFullPath(fileName)))
            {
                File.Delete(Path.GetFullPath(fileName));
            }
            workbook.SaveAs(Path.GetFullPath(fileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //4、按顺序释放资源。
            NAR(worksheet);
            NAR(sheets);
            NAR(workbook);
            NAR(workbooks);
            //自动下载
            app.Quit();
            NAR(app);
        }
        /// <summary>
        /// 导出excel后释放资源
        /// </summary>
        /// <param name="o"></param>
        private static void NAR(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch
            { }
            finally
            {
                o = null;
            }
        }

        /// <summary>
        /// 保存合并的excel
        /// </summary>
        /// <param name="fileName">保存位置</param>
        /// <param name="dt"></param>
        public static void saveCombineExcel(String fileName, DataTable dt)
        {
            List<String> columns = new List<String>();
            columns.Add("orderNo");
            columns.Add("customerName");
            columns.Add("styleNameCn");
            columns.Add("materialCode");
            columns.Add("styleSizeCd");
            columns.Add("batchQuantity");
            columns.Add("kashiyamaCreateDate");
            columns.Add("productNo");
            columns.Add("oyaProductNo");
            ExcelUtil.ToExcelTemplate(dt, @"tag\" + "xml订单.xlsx", @"doc\" + "坚山订单回执.xlsx", 4, 1, columns);
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook result = app.Workbooks.Add();
            //打开面料库存excel
            Microsoft.Office.Interop.Excel._Workbook wb面料库存 = app.Workbooks.Open(Path.GetFullPath(@"tag\" + "面料库存.xlsx"));
            //打开xml订单excel
            Microsoft.Office.Interop.Excel._Workbook wbxml订单 = app.Workbooks.Open(Path.GetFullPath(@"tag\" + "xml订单.xlsx"));

            foreach (Microsoft.Office.Interop.Excel._Worksheet each in wb面料库存.Sheets)
            {
                each.Name = "面料库存";
                each.Copy(result.Worksheets[1]);
            }
            foreach (Microsoft.Office.Interop.Excel._Worksheet each in wbxml订单.Sheets)
            {
                each.Name = "订单";
                each.Copy(result.Worksheets[1]);
            }

            wb面料库存.Close();
            wbxml订单.Close();
            //合并两个excel
            result.SaveAs(fileName);
            app.Quit();

            //删除文件
            File.Delete(Path.GetFullPath(@"tag\" + "面料库存.xlsx"));
            File.Delete(Path.GetFullPath(@"tag\" + "xml订单.xlsx"));
        }
        /// <summary>
        /// 导出excel合并多个sheet
        /// </summary>
        /// <param name="fileName"></param>
        public static void saveCombineSheet(String fileName)
        {
            //合并
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook result = app.Workbooks.Add();
            //打开收款单excel
            Microsoft.Office.Interop.Excel._Workbook wb收款单 = app.Workbooks.Open(Path.GetFullPath(@"tag\" + "收款单.xlsx"));
            //打开收款单excel
            Microsoft.Office.Interop.Excel._Workbook wb销售退款 = app.Workbooks.Open(Path.GetFullPath(@"tag\" + "销售退款.xlsx"));
            //打开退款退货excel
            Microsoft.Office.Interop.Excel._Workbook wb退款退货 = app.Workbooks.Open(Path.GetFullPath(@"tag\" + "退款退货.xlsx"));
            //打开退款未退货excel
            Microsoft.Office.Interop.Excel._Workbook wb退款未退货 = app.Workbooks.Open(Path.GetFullPath(@"tag\" + "退款未退货.xlsx"));

            foreach (Microsoft.Office.Interop.Excel._Worksheet each in wb退款未退货.Sheets)
            {
                each.Name = "退款未退货";
                each.Copy(result.Worksheets[1]);
            }
            foreach (Microsoft.Office.Interop.Excel._Worksheet each in wb退款退货.Sheets)
            {
                each.Name = "退款退货";
                each.Copy(result.Worksheets[1]);
            }
            foreach (Microsoft.Office.Interop.Excel._Worksheet each in wb销售退款.Sheets)
            {
                each.Name = "销售退款";
                each.Copy(result.Worksheets[1]);
            }
            foreach (Microsoft.Office.Interop.Excel._Worksheet each in wb收款单.Sheets)
            {
                each.Name = "销售收款";
                each.Copy(result.Worksheets[1]);
            }
            wb收款单.Close();
            wb销售退款.Close();
            wb退款退货.Close();
            wb退款未退货.Close();
            //合并两个excel
            result.SaveAs(fileName);
            app.Quit();

            //删除文件
            File.Delete(Path.GetFullPath(@"tag\" + "收款单.xlsx"));
            File.Delete(Path.GetFullPath(@"tag\" + "销售退款.xlsx"));
            File.Delete(Path.GetFullPath(@"tag\" + "退款退货.xlsx"));
            File.Delete(Path.GetFullPath(@"tag\" + "退款未退货.xlsx"));
        }
    }
    /// <summary>
    /// 数据和路径
    /// </summary>
    public class ExcelAndPath
    {
        public DataSet DataSet { get; set; }
        public String Path { get; set; }//路径
        public String Name { get; set; }//文件名
        public ExcelAndPath(DataSet dataSet,String Path,String Name)
        {
            this.DataSet = dataSet;
            this.Path = Path;
            this.Name = Name;
        }
    }
}
